const mysql = require('mysql');

// 创建数据库连接（复用数据库连接配置）
const client = mysql.createConnection({
    host: 'localhost',
    port: '3306',
    user: 'root',
    password: '123456',
    database: 'item_management'
});

// 获取所有物品列表的函数
const getItems = () => {
    return new Promise((resolve, reject) => {
        client.query('SELECT * FROM items', (err, results) => {
            if (err) {
                reject(err);
            } else {
                resolve(results);
            }
        });
    });
};

// 根据物品ID获取单个物品信息的函数
const getItemById = (itemId) => {
    return new Promise((resolve, reject) => {
        client.query('SELECT * FROM items WHERE id =?', [itemId], (err, results) => {
            if (err) {
                reject(err);
            } else {
                resolve(results.length > 0? results[0] : null);
            }
        });
    });
};

// 添加物品的函数，将新物品信息插入到数据库中
const addItem = (item) => {
    return new Promise((resolve, reject) => {
        const { name, description, quantity, category } = item;
        client.query('INSERT INTO items (name, description, quantity, category) VALUES (?,?,?,?)', [name, description, quantity, category], (err) => {
            if (err) {
                reject(err);
            } else {
                resolve();
            }
        });
    });
};

// 更新物品信息的函数，根据物品ID修改对应物品的各项信息
const updateItem = (itemId, updatedItem) => {
    return new Promise((resolve, reject) => {
        const { name, description, quantity, category } = updatedItem;
        client.query('UPDATE items SET name =?, description =?, quantity =?, category =? WHERE id =?', [name, description, quantity, category, itemId], (err) => {
            if (err) {
                reject(err);
            } else {
            resolve();
            }
        });
    });
};

// 删除物品的函数，根据物品ID从数据库中删除对应的物品记录
const deleteItem = (itemId) => {
    return new Promise((resolve, reject) => {
        client.query('DELETE FROM items WHERE id =?', [itemId], (err) => {
            if (err) {
                reject(err);
            } else {
                resolve();
            }
        });
    });
};

// 导出物品相关的数据库操作函数，供其他模块调用
module.exports = {
    getItems,
    getItemById,
    addItem,
    updateItem,
    deleteItem
};